set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

---发车准点率
insert overwrite table jms_dm.dm_ship_main_depart_summary_dt
select
     send_network_code as center_code   --当前站点编码
    ,send_network_name as center_name   --当前站点名称
    ,agent_code        as agent_code    --代理区编码
    ,agent_name        as agent_name    --代理区名称
    ,carrier_id        as carrier_id    --当前站点承运商ID
    ,carrier_name      as carrier_name  --当前站点承运商全名
    ,sum(is_shipment_flag) as yx_cnt --运行数
    ,sum(is_ontime_flag  ) as zd_cnt --准点数
    ,sum(is_shipment_flag) - sum(is_ontime_flag) as wd_cnt --晚点数
    ,sum(case when grade = 1 then is_ontime_flag else 0 end) as sj_zd_cnt --省际准点趟数
    ,sum(case when grade = 1 then is_shipment_flag - is_ontime_flag else 0 end) as sj_wd_cnt --省际晚点趟数
    ,sum(case when grade = 3 then is_ontime_flag else 0 end) as sn_zd_cnt --省内准点趟数
    ,sum(case when grade = 3 then is_shipment_flag - is_ontime_flag else 0 end) as sn_wd_cnt --省内晚点趟数
    ,dt
from jms_dm.dm_yl_shipment_trunk_detail_dt
where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
and stat_type = 1 --发车准点率
group by send_network_code    --当前站点编码
        ,send_network_name    --当前站点名称
        ,agent_code           --代理区编码
        ,agent_name           --代理区名称
        ,carrier_id           --当前站点承运商ID
        ,carrier_name         --当前站点承运商全名
        ,dt
distribute by dt,pmod(hash(rand()),5);

